/****** Object:  Table [dbo].[Idea]    Script Date: 10/12/2009 10:08:08 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[Idea] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
--~ Table [dbo].[Idea] -- Replaced column type [text] with [varchar](max)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Idea]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Idea](
	[IdeaId] [int] IDENTITY(1,1) NOT NULL,
	[CategoryId] [smallint] NOT NULL,
	[Title] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Status] [tinyint] NOT NULL,
	[Comments] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UserRatingsCount] [int] NULL,
	[AverageRating] [float] NULL,
	[FavoritesCount] [int] NULL,
	[SubmittedBy] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateSubmitted] [date] NOT NULL,
	[StatusLastUpdatedOn] [date] NULL,
 CONSTRAINT [PK_Idea] PRIMARY KEY CLUSTERED 
(
	[IdeaId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[IdeaRating]    Script Date: 10/12/2009 10:08:08 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[IdeaRating] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
--~ Table [dbo].[IdeaRating] -- Replaced column type [ntext] with [nvarchar](max)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IdeaRating]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[IdeaRating](
	[IdeaId] [int] NOT NULL,
	[UserName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Rating] [tinyint] NOT NULL,
	[Favorite] [bit] NULL,
	[Subject] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comments] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DateSubmitted] [date] NOT NULL,
 CONSTRAINT [PK_IdeaRating] PRIMARY KEY CLUSTERED 
(
	[IdeaId] ASC,
	[UserName] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[InvestmentCategory]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[InvestmentCategory] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvestmentCategory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InvestmentCategory](
	[CategoryId] [smallint] IDENTITY(1,1) NOT NULL,
	[Category] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Status] [tinyint] NULL,
	[FiscalYearBudget] [money] NULL,
	[YtdSpend] [money] NULL,
	[IdeaCount] [int] NULL,
	[CompletedProjectCount] [int] NULL,
	[ActiveProjectCount] [int] NULL,
	[StalledProjectCount] [int] NULL,
	[UserInterestCount] [int] NULL,
	[NewJobCount] [int] NULL,
	[Score] [float] NULL,
 CONSTRAINT [PK_InvestmentCategory] PRIMARY KEY CLUSTERED 
(
	[CategoryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[Project]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[Project] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
--~ Table [dbo].[Project] -- Replaced column type [text] with [varchar](max)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Project]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Project](
	[ProjectId] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Status] [tinyint] NOT NULL,
	[CategoryId] [smallint] NOT NULL,
	[ProjectManager] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[VolunteersRequired] [bit] NOT NULL,
	[EstimatedSpend] [money] NULL,
	[ActualSpend] [money] NULL,
	[StartDate] [date] NULL,
	[EstimatedEndDate] [date] NULL,
	[ActualEndDate] [date] NULL,
	[NewJobsCount] [smallint] NULL,
	[UserRatingsCount] [int] NULL,
	[AverageRating] [float] NULL,
	[FavoritesCount] [int] NULL,
	[DateCreated] [date] NOT NULL,
	[DateLastUpdated] [date] NULL,
	[StatusLastUpdatedOn] [date] NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
	[ProjectId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[ProjectIdea]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[ProjectIdea] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProjectIdea]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProjectIdea](
	[ProjectId] [int] NOT NULL,
	[IdeaId] [int] NOT NULL,
	[DateCreated] [date] NOT NULL,
 CONSTRAINT [PK_ProjectIdea] PRIMARY KEY CLUSTERED 
(
	[ProjectId] ASC,
	[IdeaId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[ProjectRating]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[ProjectRating] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
--~ Table [dbo].[ProjectRating] -- Replaced column type [ntext] with [nvarchar](max)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProjectRating]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProjectRating](
	[ProjectId] [int] NOT NULL,
	[UserName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Rating] [tinyint] NOT NULL,
	[Favorite] [bit] NULL,
	[Subject] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comments] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DateSubmitted] [date] NOT NULL,
 CONSTRAINT [PK_ProjectRating] PRIMARY KEY CLUSTERED 
(
	[ProjectId] ASC,
	[UserName] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[UserInterest]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[UserInterest] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserInterest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserInterest](
	[UserName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CategoryId] [smallint] NOT NULL,
	[Category] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_UserInterest] PRIMARY KEY CLUSTERED 
(
	[UserName] ASC,
	[CategoryId] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[UserProfile]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[UserProfile] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
--~ Table [dbo].[UserProfile] -- Replaced column type [ntext] with [nvarchar](max)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserProfile](
	[UserName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IdentityProviderUserId] [uniqueidentifier] NOT NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ThumbnailPhoto] [varbinary](max) NULL,
	[Bio] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Email] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateCreated] [date] NOT NULL,
	[DateLastUpdated] [date] NULL,
 CONSTRAINT [PK_UserProfile] PRIMARY KEY CLUSTERED 
(
	[UserName] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO
/****** Object:  Table [dbo].[Volunteer]    Script Date: 10/12/2009 10:08:09 ******/


GO
SET QUOTED_IDENTIFIER ON

GO
--~ Table [dbo].[Volunteer] -- PAD_INDEX removed.  It is not supported in this version of SQL Server
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Volunteer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Volunteer](
	[ProjectId] [int] NOT NULL,
	[UserName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateVolunteered] [date] NOT NULL,
	[Status] [tinyint] NOT NULL,
	[RoleAssigned] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[StatusUpdatedOn] [date] NULL,
 CONSTRAINT [PK_Volunteer] PRIMARY KEY CLUSTERED 
(
	[ProjectId] ASC,
	[UserName] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
